package dao.concret;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import dao.AbstractTable;
import dao.DAOList;
import dao.tables.INSTRUMENT;

public class InstrumentDAO extends DAOList<INSTRUMENT> {

    @Override
    public INSTRUMENT find(long id) {
	INSTRUMENT inst = new INSTRUMENT();
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    ResultSet result = stmt
		    .executeQuery("SELECT * FROM redline.INSTRUMENT WHERE idinstrument = "
			    + id);
	    if (result.first())
		inst = new INSTRUMENT(id, result.getString("INSTRUMENT"));
	    stmt.close();
	    result.close();
	    stmt = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return inst;
    }

    @Override
    public INSTRUMENT create(AbstractTable at) {
	INSTRUMENT obj = (INSTRUMENT) at;
	try {

	    PreparedStatement prepare = this.connect
		    .prepareStatement("INSERT INTO redline.INSTRUMENT (INSTRUMENT) VALUES (?)");
	    prepare.setString(1, obj.getValue());
	    prepare.executeUpdate();

	    ResultSet result = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE).executeQuery(
		    "SELECT SEQ_INST_IDINST.currval AS id FROM dual");

	    result.first();
	    long id = result.getLong("id");
	    obj = this.find(id);
	    prepare.close();
	    result.close();
	    prepare = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public INSTRUMENT update(AbstractTable at) {
	INSTRUMENT obj = (INSTRUMENT) at;
	try {
	    this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE).executeUpdate(
		    "UPDATE redline.INSTRUMENT SET INSTRUMENT.INSTRUMENT = '"
			    + obj + "'" + " WHERE IDINSTRUMENT = "
			    + obj.getId());
	    obj = this.find(obj.getId());
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public INSTRUMENT delete(INSTRUMENT at) {
	INSTRUMENT obj = (INSTRUMENT) at;
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    stmt.executeUpdate("DELETE FROM redline.INSTRUMENT WHERE IDINSTRUMENT = "
		    + obj.getId());
	    stmt.close();
	    stmt = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return obj;
    }

    @Override
    public INSTRUMENT[] list() {
	ArrayList<INSTRUMENT> list = new ArrayList<INSTRUMENT>();
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    ResultSet result = stmt
		    .executeQuery("SELECT IDINSTRUMENT FROM redline.INSTRUMENT");
	    while (result.next())
		list.add(this.find(result.getLong("IDINSTRUMENT")));
	    stmt.close();
	    result.close();
	    stmt = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return (INSTRUMENT[]) list.toArray(new INSTRUMENT[list.size()]);
    }

    @Override
    public INSTRUMENT[] list(long id) {
	ArrayList<INSTRUMENT> list = new ArrayList<INSTRUMENT>();
	try {
	    Statement stmt = this.connect.createStatement(
		    ResultSet.TYPE_SCROLL_INSENSITIVE,
		    ResultSet.CONCUR_UPDATABLE);
	    ResultSet result = stmt
		    .executeQuery("SELECT IDINSTRUMENT FROM redline.JOUER WHERE IDPERSONNE = "
			    + id);
	    while (result.next())
		list.add(this.find(result.getLong("IDINSTRUMENT")));
	    stmt.close();
	    result.close();
	    stmt = null;
	    result = null;
	} catch (SQLException sqle) {
	    sqle.printStackTrace();
	}
	return (INSTRUMENT[]) list.toArray(new INSTRUMENT[list.size()]);
    }
}
